package com.booleansoft.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Clob;
import java.sql.Connection; 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.booleansoft.db.ConnectFactoy;
import com.booleansoft.model.redirect.ServerStatus;
import com.booleansoft.model.redirect.SimpleEntity;
import com.booleansoft.redirect.DirectContext;


 

public class SqlHelper {
	private static SqlHelper instance=new SqlHelper();
	public static SqlHelper getInstance() {
		return instance;
	}
	private SqlHelper(){
		
	}
	private  void initsql(){
		try {
			Thread.sleep(1000);
			this.getValue("select top 1 id from transmit");
		} catch (Exception e) {
			this.execute( "create cached table transmit(" +
					"id varchar(32)," +
					"domain varchar(64)," +
					"ip varchar(32)," +
					"port integer, " +
					"discribe varchar(256)," +
					"createtime timestamp)");
			this.execute("create index transmit_domain on transmit(domain)");
		}
	}
	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args)  {
		DirectContext.getInstance();
		SqlHelper.getInstance().initsql();
	}
	public String query(String sql)  {
		return show(getValues(sql));
	}
	public  int execute(String sql){
		Connection conn =null ;
		try {
			conn = getConnection();
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace(); 
		}
		Statement stmt;
		try {
			stmt = conn.createStatement();
			int i=stmt.executeUpdate(sql);
			System.out.println(i +" rows effect ");
			stmt.close();
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(sql);
			e.printStackTrace();
		}
		close(conn);
		return -1;
	}
	public  Object getValue(String sql) {
		List list=getValues(sql);
		if(list.size()==0){
			return null;
		}
		Map map=(Map)list.get(0);
		Iterator itetator=map.entrySet().iterator();
		Map.Entry e=(Map.Entry)itetator.next();
		return e.getValue();
	}
	public  Map getQueryMap(String sql) {
		List list=getValues(sql);
		if(list.size()==0){
			return new HashMap();
		}
		return (Map)list.get(0);
	}
	public  List getValues(String sql)  {
		List list =null;
		Connection conn =null ;
		try {
			conn = getConnection();
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return new ArrayList();
		}
		PreparedStatement stmt;
		try {
//			stmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			
			list= getList(rs);
			//System.out.println("sql:"+SqlHelper.getInsertSql("menu", (Map)list.get(1)));
			rs.close();
			//helper.show(list);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			close(conn);
		}
		
		if(list==null){
			return new ArrayList();
		}
		return list;
	}
	public  String show(List list){
		StringBuffer value=new StringBuffer();
		for(int i=0;i<list.size();i++){
			Map map=(Map)list.get(i);
			Iterator iterator=map.entrySet().iterator();
//			int j=0;
			value.append(i+1);
			while(iterator.hasNext()){
				Map.Entry e=(Map.Entry)iterator.next();
				value.append("\t"+ (String)e.getKey()+":");
				Object obj=e.getValue();
				String v="";
				if(obj==null){
					v=("null");
				}else if(obj instanceof Date){
					v=(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date)obj));
				}else {
					v=(String.valueOf(obj));
				}
				value.append(v);
			}
			value.append("\r\n");
		}
		return value.toString();
	}
	public  Map  getHeader(ResultSet rs)throws SQLException{
		ResultSetMetaData rsm =rs.getMetaData(); //获得列集
		int col = rsm.getColumnCount();   //获得列的个数
		Map map=new HashMap();
		for(int i=0;i<col;i++){
			String cname=rsm.getColumnName(i+1);
			map.put(cname,cname);
		}
		return map;
	}
	public  List getList(ResultSet rs) throws SQLException{
		ResultSetMetaData rsm =rs.getMetaData(); //获得列集
		int col = rsm.getColumnCount();   //获得列的个数
		List list =new ArrayList();
		int j=0;
		while(rs.next()){
			Map map=new HashMap();
			for(int i=0;i<col;i++){
				String key=rsm.getColumnName(i+1);
				Object obj=rs.getObject(key);
				StringBuffer sb=new StringBuffer();
				if(obj instanceof Clob){
					Clob objClob=(Clob)obj;
					BufferedReader r=null;
					try {
						r= new BufferedReader(objClob.getCharacterStream());
						String s=null;
						while((s=r.readLine())!=null){
							sb.append(s);
							sb.append("\r\n");
						}
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally{
						if(r!=null){
							try {
								r.close();
							} catch (IOException e) {
							}
						}
					}
					obj=sb.toString().replaceAll("\r\n$", "");
				}
				map.put(key.toLowerCase(),obj);
			}
			list.add(map);
			if(j++>50){
				break;
			}
		}
		return list;
	}
	public  void close(Connection conn){
		try{
			conn.close();
			conn=null;
		}catch(Exception e){
		}
	}
	public Connection getConnection() throws Exception{
		ConnectFactoy conn=new ConnectFactoy();
		conn.setDriverClassName("org.hsqldb.jdbcDriver");
		conn.setUrl("jdbc:hsqldb:hsql://localhost:"+DirectContext.getHsqlport()+"/"+DirectContext.dbname);
		conn.setUsername("sa");
		conn.setPassword("123456");
		return conn.getConnection();
	} 
	 
	
	private  void listToExl(List list){
		File file=null;
		FileOutputStream fos=null;
		try {
			file = File.createTempFile("temp",".xls");
			fos=new FileOutputStream(file);
		  	for(int i=0;i<list.size();i++){
				List lists=(List)list.get(i);
				Iterator iterator=lists.iterator();
				int j=0;
				while(iterator.hasNext()){
					Object obj=iterator.next();
					if(obj instanceof Date){
						String.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date)obj));
					}else {
						String.valueOf(obj);
					}
				}
			}
			fos.close();
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
        Runtime   r   =   Runtime.getRuntime(); 
        try {
			r.exec( "cmd   /c   start   "   +   file.getAbsolutePath());
			//file.delete();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*
		
		*/
	}
	public  String getInsertSql(String table,Map map){
		StringBuffer sb=new StringBuffer();
		sb.append("insert into ").append(table).append("  ");
		StringBuffer key=new StringBuffer();
		StringBuffer value=new StringBuffer();
		Iterator iterator=map.entrySet().iterator();
		while(iterator.hasNext()){
			Map.Entry<String , Object> entry=(Map.Entry<String , Object>)iterator.next();
			key.append(entry.getKey()).append(",");
			Object obj=entry.getValue();
			if(obj==null){
				value.append(" null");
			}else if(obj instanceof String){
				value.append("'").append(String.valueOf(obj).replace("'", "''")).append("'");
			}else if(obj instanceof Integer){
				value.append(((Integer) obj).intValue());
			}else if(obj instanceof Float){
				value.append(((Float) obj).floatValue());
			}else if(obj instanceof Date){
				value.append(" '"+DateHelper.getStrFromDate((Date)obj)+"' ");
			}else {
				value.append("'").append(String.valueOf(obj)).append("'");
			}
			value.append(",");
		}
		return sb.append("(").append(key.toString().replaceAll(",\\s*$", "")).append(")")
				.append(" values ")
				.append("(").append(value.toString().replaceAll(",\\s*$", "")).append(")").toString();
	}
	
	public  String getInsertSql(SimpleEntity bean){
		return getInsertSql(bean.getObjectType(), bean.getBeanMap());
	}
	public  String getUpdateSql(String table,Map map,String sqlwhere){
		StringBuffer sb=new StringBuffer();
		sb.append("update ").append(table).append(" set ");
		StringBuffer value=new StringBuffer();
		Iterator iterator=map.entrySet().iterator();
		while(iterator.hasNext()){
			Map.Entry<String , Object> entry=(Map.Entry<String , Object>)iterator.next();
			if(entry.getKey().equals("id")){
				continue;
			}
			value.append(entry.getKey()).append("=");
			Object obj=entry.getValue();
			if(obj==null){
				value.append(" null ");
			}else if(obj instanceof String){
				value.append("'").append(String.valueOf(obj).replace("'", "''")).append("'");
			}else if(obj instanceof Integer){
				value.append(((Integer) obj).intValue()).append("");
			}else if(obj instanceof Float){
				value.append(((Float) obj).floatValue()).append("");
			}else if(obj instanceof Date){
					value.append(" '"+DateHelper.getStrFromDate((Date)obj)+"' ");
			}else {
				value.append("'").append(String.valueOf(obj)).append("'");
			}
			value.append(",");
		}
		sb.append(value.toString().replaceAll(",\\s*$", ""));
		if(!StringHelper.isEmpty(sqlwhere)){
			sb.append(" where ").append(sqlwhere).toString();
		}
		return sb.toString();
	} 
	public  String getUpdateSql(SimpleEntity bean){
		if(StringHelper.isEmpty(bean.getId())){
			return null;
		}
		return getUpdateSql(bean.getObjectType(), bean.getBeanMap()," id='"+bean.getId()+"' ");
	}
}
